
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 04/19/2011 14:55:25
-- Generated from EDMX file: C:\Users\Alexandra.CHASTAIN\Documents\Visual Studio 2010\Projects\Defecter(19)\Defecter\DefectDB\DefectDB.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [DefectEntities];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Trouble_ReportPerson]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Trouble_Report] DROP CONSTRAINT [FK_Trouble_ReportPerson];
GO
IF OBJECT_ID(N'[dbo].[FK_SoftwareTrouble_Report]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Trouble_Report] DROP CONSTRAINT [FK_SoftwareTrouble_Report];
GO
IF OBJECT_ID(N'[dbo].[FK_CompanyClient]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[People_Client] DROP CONSTRAINT [FK_CompanyClient];
GO
IF OBJECT_ID(N'[dbo].[FK_PersonStatus_History]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Status_History] DROP CONSTRAINT [FK_PersonStatus_History];
GO
IF OBJECT_ID(N'[dbo].[FK_Trouble_ReportStatus_History]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Status_History] DROP CONSTRAINT [FK_Trouble_ReportStatus_History];
GO
IF OBJECT_ID(N'[dbo].[FK_CompanySoftware_Company]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CompanySoftware] DROP CONSTRAINT [FK_CompanySoftware_Company];
GO
IF OBJECT_ID(N'[dbo].[FK_CompanySoftware_Software]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CompanySoftware] DROP CONSTRAINT [FK_CompanySoftware_Software];
GO
IF OBJECT_ID(N'[dbo].[FK_CommentPerson]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Comments] DROP CONSTRAINT [FK_CommentPerson];
GO
IF OBJECT_ID(N'[dbo].[FK_Trouble_ReportComment]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Comments] DROP CONSTRAINT [FK_Trouble_ReportComment];
GO
IF OBJECT_ID(N'[dbo].[FK_Client_inherits_Person]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[People_Client] DROP CONSTRAINT [FK_Client_inherits_Person];
GO
IF OBJECT_ID(N'[dbo].[FK_Developer_inherits_Person]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[People_Developer] DROP CONSTRAINT [FK_Developer_inherits_Person];
GO
IF OBJECT_ID(N'[dbo].[FK_Tester_inherits_Person]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[People_Tester] DROP CONSTRAINT [FK_Tester_inherits_Person];
GO
IF OBJECT_ID(N'[dbo].[FK_Manager_inherits_Person]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[People_Manager] DROP CONSTRAINT [FK_Manager_inherits_Person];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[People]', 'U') IS NOT NULL
    DROP TABLE [dbo].[People];
GO
IF OBJECT_ID(N'[dbo].[Companies]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Companies];
GO
IF OBJECT_ID(N'[dbo].[Softwares]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Softwares];
GO
IF OBJECT_ID(N'[dbo].[Trouble_Report]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Trouble_Report];
GO
IF OBJECT_ID(N'[dbo].[Status_History]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Status_History];
GO
IF OBJECT_ID(N'[dbo].[Comments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Comments];
GO
IF OBJECT_ID(N'[dbo].[Glossaries]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Glossaries];
GO
IF OBJECT_ID(N'[dbo].[People_Client]', 'U') IS NOT NULL
    DROP TABLE [dbo].[People_Client];
GO
IF OBJECT_ID(N'[dbo].[People_Developer]', 'U') IS NOT NULL
    DROP TABLE [dbo].[People_Developer];
GO
IF OBJECT_ID(N'[dbo].[People_Tester]', 'U') IS NOT NULL
    DROP TABLE [dbo].[People_Tester];
GO
IF OBJECT_ID(N'[dbo].[People_Manager]', 'U') IS NOT NULL
    DROP TABLE [dbo].[People_Manager];
GO
IF OBJECT_ID(N'[dbo].[CompanySoftware]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CompanySoftware];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'People'
CREATE TABLE [dbo].[People] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [First_Name] nvarchar(max)  NOT NULL,
    [Last_Name] nvarchar(max)  NOT NULL,
    [Address] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [Phone_Number] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Companies'
CREATE TABLE [dbo].[Companies] (
    [Company_Id] int IDENTITY(1,1) NOT NULL,
    [Company_Name] nvarchar(max)  NOT NULL,
    [Company_Phone] nvarchar(max)  NOT NULL,
    [Company_Address] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Softwares'
CREATE TABLE [dbo].[Softwares] (
    [Software_Id] int IDENTITY(1,1) NOT NULL,
    [Software_Name] nvarchar(max)  NOT NULL,
    [Software_Release_Date] nvarchar(max)  NOT NULL,
    [Software_version_number] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Trouble_Report'
CREATE TABLE [dbo].[Trouble_Report] (
    [Trouble_Report_Id] int IDENTITY(1,1) NOT NULL,
    [Date_Submitted] nvarchar(max)  NOT NULL,
    [PersonId] int  NOT NULL,
    [Headline] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [SoftwareSoftware_Id] int  NOT NULL,
    [Date_Completed] nvarchar(max)  NOT NULL,
    [Priority] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Status_History'
CREATE TABLE [dbo].[Status_History] (
    [Status_Id] int IDENTITY(1,1) NOT NULL,
    [trouble_report_phase] nvarchar(max)  NOT NULL,
    [Date] nvarchar(max)  NOT NULL,
    [PersonId] int  NOT NULL,
    [Trouble_ReportTrouble_Report_Id] int  NOT NULL,
    [Details] nvarchar(max)  NOT NULL,
    [Hours_Per_Phase] nvarchar(max)  NOT NULL,
    [Priority_Change] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Comments'
CREATE TABLE [dbo].[Comments] (
    [Comment_Id] int IDENTITY(1,1) NOT NULL,
    [PersonId] int  NOT NULL,
    [Trouble_ReportTrouble_Report_Id] int  NOT NULL,
    [Date] nvarchar(max)  NOT NULL,
    [Details] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Glossaries'
CREATE TABLE [dbo].[Glossaries] (
    [Term] nvarchar(max)  NOT NULL,
    [Definition] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Messages'
CREATE TABLE [dbo].[Messages] (
    [Message_Id] int IDENTITY(1,1) NOT NULL,
    [Subject] nvarchar(max)  NOT NULL,
    [Content] nvarchar(max)  NOT NULL,
    [From] nvarchar(max)  NOT NULL,
    [IsNew] varbinary(max)  NOT NULL,
    [PersonId] int  NOT NULL
);
GO

-- Creating table 'People_Client'
CREATE TABLE [dbo].[People_Client] (
    [CompanyCompany_Id] int  NOT NULL,
    [Id] int  NOT NULL
);
GO

-- Creating table 'People_Developer'
CREATE TABLE [dbo].[People_Developer] (
    [Id] int  NOT NULL
);
GO

-- Creating table 'People_Tester'
CREATE TABLE [dbo].[People_Tester] (
    [Id] int  NOT NULL
);
GO

-- Creating table 'People_Manager'
CREATE TABLE [dbo].[People_Manager] (
    [Id] int  NOT NULL
);
GO

-- Creating table 'CompanySoftware'
CREATE TABLE [dbo].[CompanySoftware] (
    [Companies_Company_Id] int  NOT NULL,
    [Softwares_Software_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'People'
ALTER TABLE [dbo].[People]
ADD CONSTRAINT [PK_People]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Company_Id] in table 'Companies'
ALTER TABLE [dbo].[Companies]
ADD CONSTRAINT [PK_Companies]
    PRIMARY KEY CLUSTERED ([Company_Id] ASC);
GO

-- Creating primary key on [Software_Id] in table 'Softwares'
ALTER TABLE [dbo].[Softwares]
ADD CONSTRAINT [PK_Softwares]
    PRIMARY KEY CLUSTERED ([Software_Id] ASC);
GO

-- Creating primary key on [Trouble_Report_Id] in table 'Trouble_Report'
ALTER TABLE [dbo].[Trouble_Report]
ADD CONSTRAINT [PK_Trouble_Report]
    PRIMARY KEY CLUSTERED ([Trouble_Report_Id] ASC);
GO

-- Creating primary key on [Status_Id] in table 'Status_History'
ALTER TABLE [dbo].[Status_History]
ADD CONSTRAINT [PK_Status_History]
    PRIMARY KEY CLUSTERED ([Status_Id] ASC);
GO

-- Creating primary key on [Comment_Id] in table 'Comments'
ALTER TABLE [dbo].[Comments]
ADD CONSTRAINT [PK_Comments]
    PRIMARY KEY CLUSTERED ([Comment_Id] ASC);
GO

-- Creating primary key on [Term] in table 'Glossaries'
ALTER TABLE [dbo].[Glossaries]
ADD CONSTRAINT [PK_Glossaries]
    PRIMARY KEY CLUSTERED ([Term] ASC);
GO

-- Creating primary key on [Message_Id] in table 'Messages'
ALTER TABLE [dbo].[Messages]
ADD CONSTRAINT [PK_Messages]
    PRIMARY KEY CLUSTERED ([Message_Id] ASC);
GO

-- Creating primary key on [Id] in table 'People_Client'
ALTER TABLE [dbo].[People_Client]
ADD CONSTRAINT [PK_People_Client]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'People_Developer'
ALTER TABLE [dbo].[People_Developer]
ADD CONSTRAINT [PK_People_Developer]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'People_Tester'
ALTER TABLE [dbo].[People_Tester]
ADD CONSTRAINT [PK_People_Tester]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'People_Manager'
ALTER TABLE [dbo].[People_Manager]
ADD CONSTRAINT [PK_People_Manager]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Companies_Company_Id], [Softwares_Software_Id] in table 'CompanySoftware'
ALTER TABLE [dbo].[CompanySoftware]
ADD CONSTRAINT [PK_CompanySoftware]
    PRIMARY KEY NONCLUSTERED ([Companies_Company_Id], [Softwares_Software_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [PersonId] in table 'Trouble_Report'
ALTER TABLE [dbo].[Trouble_Report]
ADD CONSTRAINT [FK_Trouble_ReportPerson]
    FOREIGN KEY ([PersonId])
    REFERENCES [dbo].[People]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Trouble_ReportPerson'
CREATE INDEX [IX_FK_Trouble_ReportPerson]
ON [dbo].[Trouble_Report]
    ([PersonId]);
GO

-- Creating foreign key on [SoftwareSoftware_Id] in table 'Trouble_Report'
ALTER TABLE [dbo].[Trouble_Report]
ADD CONSTRAINT [FK_SoftwareTrouble_Report]
    FOREIGN KEY ([SoftwareSoftware_Id])
    REFERENCES [dbo].[Softwares]
        ([Software_Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SoftwareTrouble_Report'
CREATE INDEX [IX_FK_SoftwareTrouble_Report]
ON [dbo].[Trouble_Report]
    ([SoftwareSoftware_Id]);
GO

-- Creating foreign key on [CompanyCompany_Id] in table 'People_Client'
ALTER TABLE [dbo].[People_Client]
ADD CONSTRAINT [FK_CompanyClient]
    FOREIGN KEY ([CompanyCompany_Id])
    REFERENCES [dbo].[Companies]
        ([Company_Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CompanyClient'
CREATE INDEX [IX_FK_CompanyClient]
ON [dbo].[People_Client]
    ([CompanyCompany_Id]);
GO

-- Creating foreign key on [PersonId] in table 'Status_History'
ALTER TABLE [dbo].[Status_History]
ADD CONSTRAINT [FK_PersonStatus_History]
    FOREIGN KEY ([PersonId])
    REFERENCES [dbo].[People]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PersonStatus_History'
CREATE INDEX [IX_FK_PersonStatus_History]
ON [dbo].[Status_History]
    ([PersonId]);
GO

-- Creating foreign key on [Trouble_ReportTrouble_Report_Id] in table 'Status_History'
ALTER TABLE [dbo].[Status_History]
ADD CONSTRAINT [FK_Trouble_ReportStatus_History]
    FOREIGN KEY ([Trouble_ReportTrouble_Report_Id])
    REFERENCES [dbo].[Trouble_Report]
        ([Trouble_Report_Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Trouble_ReportStatus_History'
CREATE INDEX [IX_FK_Trouble_ReportStatus_History]
ON [dbo].[Status_History]
    ([Trouble_ReportTrouble_Report_Id]);
GO

-- Creating foreign key on [Companies_Company_Id] in table 'CompanySoftware'
ALTER TABLE [dbo].[CompanySoftware]
ADD CONSTRAINT [FK_CompanySoftware_Company]
    FOREIGN KEY ([Companies_Company_Id])
    REFERENCES [dbo].[Companies]
        ([Company_Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Softwares_Software_Id] in table 'CompanySoftware'
ALTER TABLE [dbo].[CompanySoftware]
ADD CONSTRAINT [FK_CompanySoftware_Software]
    FOREIGN KEY ([Softwares_Software_Id])
    REFERENCES [dbo].[Softwares]
        ([Software_Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CompanySoftware_Software'
CREATE INDEX [IX_FK_CompanySoftware_Software]
ON [dbo].[CompanySoftware]
    ([Softwares_Software_Id]);
GO

-- Creating foreign key on [PersonId] in table 'Comments'
ALTER TABLE [dbo].[Comments]
ADD CONSTRAINT [FK_CommentPerson]
    FOREIGN KEY ([PersonId])
    REFERENCES [dbo].[People]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CommentPerson'
CREATE INDEX [IX_FK_CommentPerson]
ON [dbo].[Comments]
    ([PersonId]);
GO

-- Creating foreign key on [Trouble_ReportTrouble_Report_Id] in table 'Comments'
ALTER TABLE [dbo].[Comments]
ADD CONSTRAINT [FK_Trouble_ReportComment]
    FOREIGN KEY ([Trouble_ReportTrouble_Report_Id])
    REFERENCES [dbo].[Trouble_Report]
        ([Trouble_Report_Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Trouble_ReportComment'
CREATE INDEX [IX_FK_Trouble_ReportComment]
ON [dbo].[Comments]
    ([Trouble_ReportTrouble_Report_Id]);
GO

-- Creating foreign key on [PersonId] in table 'Messages'
ALTER TABLE [dbo].[Messages]
ADD CONSTRAINT [FK_PersonMessage]
    FOREIGN KEY ([PersonId])
    REFERENCES [dbo].[People]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PersonMessage'
CREATE INDEX [IX_FK_PersonMessage]
ON [dbo].[Messages]
    ([PersonId]);
GO

-- Creating foreign key on [Id] in table 'People_Client'
ALTER TABLE [dbo].[People_Client]
ADD CONSTRAINT [FK_Client_inherits_Person]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[People]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id] in table 'People_Developer'
ALTER TABLE [dbo].[People_Developer]
ADD CONSTRAINT [FK_Developer_inherits_Person]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[People]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id] in table 'People_Tester'
ALTER TABLE [dbo].[People_Tester]
ADD CONSTRAINT [FK_Tester_inherits_Person]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[People]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id] in table 'People_Manager'
ALTER TABLE [dbo].[People_Manager]
ADD CONSTRAINT [FK_Manager_inherits_Person]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[People]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------